package cap.dao.impl;

import cap.bean.Category;
import cap.dao.CategoryDao;
import cap.db.DBPool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CategoryDaoImpl implements CategoryDao {
	Connection conn=null;
	PreparedStatement ps=null;
	ResultSet rs=null;
	/**
	 *根据用户id获取所有分类
	 */
	@Override
	public List<Category> getByUserId(int userId) {
		List<Category> cgList = null;
		try {
			String sql="select * from category where user_id=? and is_delete=0";
			cgList = new ArrayList<Category>();
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1,userId);
			rs=ps.executeQuery();
			while (rs.next()) {
				Category cg = new Category();
				cg.setId(rs.getInt("id"));
				cg.setUserId(rs.getInt("user_id"));
				cg.setCategoryName(rs.getString("category_name"));
				cg.setArticles(rs.getInt("articals"));
				cg.setIsDelete(rs.getInt("is_delete"));
				cgList.add(cg);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBPool.close(rs,ps,conn);
		}
		return cgList;
	}
	/**
	 *根据id获取分类
	 */
	@Override
	public Category getById(int id) {
		Category cg = null;
		try {
			String sql="select * from category where id=?";
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1,id);
			rs=ps.executeQuery();
			if (rs.next()) {
				cg = new Category();
				cg.setId(rs.getInt("id"));
				cg.setUserId(rs.getInt("user_id"));
				cg.setCategoryName(rs.getString("category_name"));
				cg.setArticles(rs.getInt("articals"));
				cg.setIsDelete(rs.getInt("is_delete"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBPool.close(rs,ps,conn);
		}
		return cg;
	}
	/**
	 *根据id删除类别
	 */
	@Override
	public int deleteCategory(int cgId) {
		int res = -1;

		try {
			String sql="update category set is_delete=1 where id=?";
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1,cgId);
			res=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBPool.close(rs,ps,conn);
		}
		return res;
	}
	/**
	 *根据类别名和用户id获取类别
	 */
	@Override
	public Category getByName(String cgName, int userId) {
		Category cg = null;
		try {
			String sql="select * from category where category_name=? and user_id=? and is_delete=0";
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			ps.setString(1,cgName);
			ps.setInt(2,userId);
			rs=ps.executeQuery();
			if (rs.next()) {
				cg = new Category();
				cg.setId(rs.getInt("id"));
				cg.setUserId(rs.getInt("user_id"));
				cg.setCategoryName(rs.getString("category_name"));
				cg.setArticles(rs.getInt("articals"));
				cg.setIsDelete(rs.getInt("is_delete"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBPool.close(rs,ps,conn);
		}
		return cg;
	}
	/**
	 *添加类别
	 */
	@Override
	public int insertCategory(int userId, String cgName) {
		int res = -1;
		DBPool dbc=new DBPool();
		try {
			String sql="insert into category values(NULL, ?, ?, 0, 0)";
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1,userId);
			ps.setString(2,cgName);
			res=ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBPool.close(rs,ps,conn);
		}

		return res;
	}
	/**
	 *根据id修改类名
	 */
	@Override
	public int updateCategory(int cgId, String cgName) {

		int res = -1;
		try {
			String sql="update category set category_name=? where id=?";
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			ps.setString(1,cgName);
			ps.setInt(2,cgId);
			res=ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBPool.close(rs,ps,conn);
		}
		return res;
	}
	/**
	 *分页显示类别
	 */
	@Override
	public List<Category> getCategoryByPage(int curPage, int size) {
		List<Category> cgList = null;
		try {
			String sql="select * from category where is_delete=0 order by id DESC limit ?,?";
			cgList = new ArrayList<Category>();
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1,curPage);
			ps.setInt(2,size);
			rs=ps.executeQuery();
			while (rs.next()) {
				Category cg = new Category();
				cg.setId(rs.getInt("id"));
				cg.setUserId(rs.getInt("user_id"));
				cg.setCategoryName(rs.getString("category_name"));
				cg.setArticles(rs.getInt("articals"));
				cg.setIsDelete(rs.getInt("is_delete"));
				cgList.add(cg);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBPool.close(rs,ps,conn);
		}
		return cgList;
	}
	/**
	 *根据用户id分页显示类别
	 */
	@Override
	public List<Category> getCategoryByPageUserId(int curPage, int size, int userId) {
		List<Category> cgList = null;
		try {
			String sql="select * from category where is_delete=0 and user_id=? order by id DESC limit ?,?";
			cgList = new ArrayList<Category>();
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1,userId);
			ps.setInt(2,curPage);
			ps.setInt(3,size);
			rs=ps.executeQuery();
			while (rs.next()) {
				Category cg = new Category();
				cg.setId(rs.getInt("id"));
				cg.setUserId(rs.getInt("user_id"));
				cg.setCategoryName(rs.getString("category_name"));
				cg.setArticles(rs.getInt("articals"));
				cg.setIsDelete(rs.getInt("is_delete"));
				cgList.add(cg);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBPool.close(rs,ps,conn);
		}
		return cgList;
	}

}
